----------------------------------------------------------------------------
if OBJECT_ID('pp_FormatString', 'FN') > 0  drop function  pp_FormatString
----------------------------------------------------------------------------
go
create function dbo.pp_FormatString ( 
	@s      varchar(4000), 
	@flag_o bit,            -- Oracle: replace '&' by ' || chr(38) || '
	@flag_s bit             -- split long XML lines @ '/><'
) returns varchar(4000)
as
-- Bernd Kriszio
-- https://twitter.com/SQLSulidae  2010-10-22
-- general: doublicate embedded apostrophes
begin
	return case when  @flag_o <> 0 and @flag_s <> 0  then replace(replace(replace(@s, '''', ''''''), '&', ''' || chr(38) || ''') ,'><' , '>' + char(13) + char (10) + '<')
	            when  @flag_o <> 0 and @flag_s = 0   then replace(replace(@s, '''', ''''''), '&', ''' || chr(38) || ''')
	            when  @flag_o = 0  and @flag_s <> 0  then replace(replace(@s, '''', '''''') ,'/><' , '/>' + char(13) + char (10) + '<')
	                                                 else replace(@s, '''', '''''')
	           end
end
go

/*
select dbo.pp_FormatString ('&as''df&ef', 0, 0)
select dbo.pp_FormatString ('&as''df&ef', 1)
select dbo.pp_FormatString ('&as''df&ef/><efg', 0, 0)
select dbo.pp_FormatString ('&as''df&ef/><efg', 0, 1)
*/

----------------------------------------------------------------------------
if OBJECT_ID('sp_gentextupdate', 'P') > 0  drop procedure sp_gentextupdate
----------------------------------------------------------------------------
go

create procedure dbo.sp_gentextupdate(
	@table sysname,
	@key sysname,
	@column sysname,
	@cond varchar(8000) = null,
	@flags varchar(10) = '',
	@to varchar(200) = ''
) as
-- Bernd Kriszio
-- https://twitter.com/SQLSulidae  2010-10-29
-- requires SQL Server 2008: varchar(max) / port to SQL Server 2005 would be not trivial
begin
	set nocount on
	set concat_null_yields_null on
	
	declare @flag_o    bit	= case when charindex ('O', @FLAGS) > 0	then 1 else 0 end -- ORACLE
	DECLARE @flag_t    bit  = case when charindex ('T',  @FLAGS) > 0	then 1 else 0 end -- use transactions
	declare @flag_v    bit	= case when charindex ('V', @FLAGS) > 0	then 1 else 0 end -- verbose
	declare @flag_x    bit	= case when charindex ('X', @FLAGS) > 0	then 1 else 0 end -- split XML

	if @cond = '' set @cond = null
	if @to = ''   set @to = @table
	 	
	declare @ref int
	declare @text varchar(max)
	declare @line varchar(4000)
	declare @pos int
	declare @preamble varchar(200)
	declare @epilog varchar(200)
	declare @maxlinelength int = 0
	declare @initialLength varchar(10)
	Declare @dl int

	DECLARE @statement_count int 
	declare @linecount int
	declare @formattedLine varchar(4000)
	declare @crnl char(2) = CHAR(13) + CHAR(10)
	

	declare @cmd varchar(max) =  'Select ' + @key + ', ' + @column + ' from ' + @table + ' where ' +  @column + ' is not null' + isnull (' and ' + @cond, '')
	declare @work table (ref int, content varchar(max)) 
	Insert into @work exec (@cmd)
	if (select COUNT(1) from @work ) = 0 
	begin
		Print 'No data found: ' + @cmd
		return
	end	
	
	-- dbms specific settings and output for generated script header
	if @flag_o <> 0
	begin
		Set @preamble = 'v_clob := '''
		Set @epilog = ''';'
	end
	else
	begin
		PRINT 'SET NOCOUNT ON'
		PRINT ''
		Set @preamble = 'UPDATE ' + @to + ' SET ' + @column + ' = '''
		Set @epilog = '''' 
	end
	SET @statement_count = 0

	declare c cursor for
		select ref, content from @work
	open c
	fetch next from c into @ref, @text
	while @@fetch_status = 0
	begin
		SET @statement_count = @statement_count + 1

		set @initialLength = str(datalength(@text))
		-----------------------------------------------------
		Print '-- Refnr:' + str(@ref) + ' datalength: ' + @initialLength
		-----------------------------------------------------
		-- fix inconsistent lineendings
		set @text = replace(	replace(@text, @crnl, CHAR(10))  , char(13), CHAR(10)  )
		Set @linecount = 0
		Set @dl = 0
		if @flag_o <> 0 and Datalength(@text) > 0
		begin
			print 'DECLARE v_clob CLOB;'
			print 'BEGIN'
		end
		while (Datalength(@text) > 0)
		begin
			set @pos = charindex(CHAR(10), @text)
			if @pos > 0
			begin
				Set @line = LEFT (@text , @pos - 1)
				set @maxlinelength = case when @maxlinelength > DATALENGTH(@line) then @maxlinelength else  DATALENGTH(@line) end
				----------------------------------------------------------------------------------------------------------------------------------
				if @flag_v <> 0   print 'line ' + str(@linecount) + ' ' + str(@pos) + '  ' + str( datalength(@text)) + ' ' + str(datalength(@line))
				----------------------------------------------------------------------------------------------------------------------------------
				SET @formattedLine =  dbo.pp_FormatString(@line, @flag_o, @flag_x)
				Set @linecount = @linecount + 1
				if @linecount = 1
				begin
					Print @preamble  + @formattedLine 
					set @dl = @dl + LEN( @formattedLine) + 2
				end
				else
				begin	 	
					If @dl + LEN( @formattedLine) > 3950 and @flag_o <> 0 
					begin
						set @formattedLine =  '''; ' + @crnl + 
						'-- Stringlength: ' + STR(@dl)  + @crnl + 
						'v_clob := v_clob || ''' + @formattedLine
						set @dl = 0
					end	
					set @dl = @dl + LEN( @formattedLine) + 2
					Print @formattedLine 
				end 	
				Set @text = SUBSTRING(@text, @pos + 1, 10000000)                                        -- maxlength
				----------------------------------------------------------------------------------------------------------------------------------
				if @flag_v <> 0   print 'line ' +str(@linecount) + ' ' + str(@pos) + '  ' + str( datalength(@text)) + ' ' + str(datalength(@line))
				----------------------------------------------------------------------------------------------------------------------------------
			end
			else
			begin
				SET @formattedLine =  dbo.pp_FormatString(@text, @flag_o, @flag_x)
				Print case when @linecount = 0 then @preamble else '' end + @formattedLine +  @epilog
				set @dl = @dl + LEN( @formattedLine)
				IF @flag_o <> 0
				begin
					Print 'UPDATE ' + @to + ' SET '  + @column + ' = v_clob'
					print 'WHERE ' + @key + ' = ' + str(@ref) + ';'
					Print 'END;'
					Print '/'
				end
				else
					print 'WHERE ' + @key + ' = ' + str(@ref) + ';'
				
				print '-- Linecount: ' + str(@linecount) + '  StringLength: ' + str(@DL)
				print ''
				
				set @text = ''
			end				
		end
		if  @statement_count % 100 = 0 and @flag_o <> 0
		begin
			PRINT 'COMMIT;'
			PRINT 'PROMPT ' + str(@statement_count) + ';'
			PRINT ''
		end		
	fetch next from c into @ref, @text
	end
	close c
	deallocate c
	
	print '-- Max Linelength: ' + str(@maxlinelength)
end
go

